package application.models.datalayer;


import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import application.main.Application;
import application.models.exceptions.DataTypeException;

/**
 * This class handles any communication with a SQLite database. It is a subclass of the DataLayerCommunicator class
 * 
 * @author Philip Rasmussen & Morten Therkildsen
 *
 */
public class DatabaseCommunicator extends DataLayerCommunicator {

	private static final String DB_PATH = Application.getResourceDirectoryPath()+"DB.sqlite";
	
	private static DatabaseCommunicator instance;
	
	/**
	 * Returns a DatabaseCommunicator (Singleton pattern)
	 * A new one is created at first call
	 * Subsequent calls reuses the already created instance
	 * 
	 * @return	a DatabaseCommunicator
	 */
	public static DatabaseCommunicator getInstance(){
		
		if(instance != null) return instance;
		
		instance = new DatabaseCommunicator();
		return instance;
	}
	
	/**
	 * Converts the data type of the parameter value to an SQL compliant data type string
	 * @param The value whose data type is to be converted to an SQL compliant data type string
	 * @return The converted SQL compliant data type string. Can be null.
	 */
	private static String objectToSqlString(Object value){

		if(value != null){

			if(!(value instanceof Integer) && !(value instanceof Date)) return "\""+value.toString()+"\""; // String, Boolean
			else return value.toString(); // Integer, Date
		} else return "NULL"; // Null
	}

	/**
	 * Converts a string value to an object of dataType containing the value 
	 * @param value The string representation of the value
	 * @param dataType The data type to convert value to
	 * @return The object of class dataType with value
	 * @throws DataTypeException	if the passed dataType is invalid
	 */
	private static Object stringToAttributeValueObject(String value, String dataType) throws DataTypeException {

		if(value == null) return null;
		try {
			Class<?> dataTypeClass = Attribute.getClassForDataType(dataType);
			return dataTypeClass.getConstructor(Class.forName("java.lang.String")).newInstance(value);
		} catch(InstantiationException e){ throw new RuntimeException("Failed to convert data type to its mapped Java object.", e);
		} catch(ClassNotFoundException e){ throw new RuntimeException("Unexpected logic error: The class java.lang.Object could not be found.", e);
		} catch (IllegalAccessException e){ throw new RuntimeException("Failed to convert data layer data type value to a java object.", e);
		} catch (InvocationTargetException e){ throw new RuntimeException("Failed to convert data layer data type value to a java object.", e.getCause());
		} catch (NoSuchMethodException e){ throw new RuntimeException("Failed to convert data layer data type value to a java object.", e);
		}
	}

	private final Connection connection;	// The connection to the data layer
	private final Statement statement;		// Used for queries

	/**
	 * Loads the data base driver, attempts to establish connection, and initializes statement (preparing it for queries).
	 * Enforces the use of getInstance()
	 */
	private DatabaseCommunicator(){

		try{

			Class.forName("org.sqlite.JDBC"); // Load driver
			connection = DriverManager.getConnection("jdbc:sqlite:"+DB_PATH); // Establish connection
			statement = connection.createStatement(); // Prepare a statement for future queries
		} catch(ClassNotFoundException e){	throw new RuntimeException("The class org.sqlite.JDBC was not found.", e);
		} catch(SQLException e){ throw new RuntimeException("Failed to connect to the database!", e);
		}
	}

	/**
	 * Get meta information about the attributes of a data set (quantity, their data types, values, etc.)
	 * 
	 * @param setName		The name of the set to retrieve the information for
	 * @return				An array of attribute objects/descriptions
	 * @throws IOException if the operation fails
	 */
	public Attribute[] getDataSetDescription(String setName) throws IOException {

		if(setName == null) throw new NullPointerException("setName was null.");
		if(setName.trim().length() == 0) throw new IllegalArgumentException("setName was empty.");

		try{

			DatabaseMetaData meta = connection.getMetaData();

			ResultSet results = meta.getColumns(null, null, setName, null);
			
			
			
			ArrayList<Attribute> attributes = new ArrayList<Attribute>();
			
			while(results.next()){

				attributes.add(new Attribute(
						results.getString("TYPE_NAME"),
						results.getString("COLUMN_NAME"),
						setName,
						// Does make the assumption that AUTO_INCREMENT fields allows null because the driver misses functionality
						results.getString("IS_NULLABLE").equals("N"), // && results.getString("IS_AUTOINCREMENT").equals("N")
						null
				));
			}
			
			results.close();

			return attributes.toArray(new Attribute[0]);

		} catch(SQLException e){throw new IOException("Failed to retrieve a description of the data set.", e);
		} catch(DataTypeException e){throw new IOException("Failed to retrieve a description of the data set. Unexpected logic error.", e);
		}
	}

	/**
	 * Queries the data layer for data. Returns all attributes for the matched entity
	 * 
	 * @param setName		The name of the set to retrieve information for. Cannot be null or empty.
	 * @param conditions	Any conditions used to narrow down the results.
	 * @return				A 2-dimensional array containing the attribute objects that satisfies conditions
	 * @throws IOException if the operation fails
	 */
	public Attribute[][] readFromDataLayer(String setName, DataCondition[] conditions) throws IOException{

		return readFromDataLayer(setName, conditions, null);
	}

	/**
	 * Queries the data layer for data. Returns only the specified attributes for the matched entities.
	 * If no attributes are specified (null), all will be queried.
	 * 
	 * @param setName		The name of the set to retrieve information for. Cannot be null or empty.
	 * @param conditions	Any conditions used to narrow down the results.
	 * @param attributeNames	The names of the attribute objects to return
	 * @return				A 2-dimensional array containing the attribute objects defined by attributeNames that satisfies conditions
	 * @throws IOException if the operation fails
	 */
	public Attribute[][] readFromDataLayer(String setName, DataCondition[] conditions, String[] attributeNames) throws IOException {

		if(setName == null) throw new NullPointerException("setName was null.");
		if(setName.trim().length() == 0) throw new IllegalArgumentException("setName was empty.");

		if(conditions == null) conditions = new DataCondition[0]; // Set default value

		// STEP 1: GENERATE SQL QUERY STRING		
		String what = "";
		if(attributeNames != null){

			for(String attributeName : attributeNames){

				if(attributeName != null && attributeName.trim().length() > 0) what += "`"+attributeName+"`, ";
			}
			what = what.substring(0, what.length()-2); // Remove last ", "

		} else what = "*";

		// WHERE
		String where = sqlWhere(conditions, "AND");

		// COMBINED
		String sql = "SELECT "+what+" FROM `"+setName+"`"+(conditions.length > 0 ? where : "")+";";

		// STEP 2: QUERY DATABASE AND PROCESS RESULTS

		try{
			
			ResultSet results = statement.executeQuery(sql);

			ResultSetMetaData meta = results.getMetaData();

			int attributeCount = meta.getColumnCount();

			ArrayList<Attribute[]> entities = new ArrayList<Attribute[]>();
			
			// Needed because of the driver bug mentioned below
			Map<String, String> attributeDataTypes = new HashMap<String, String>();
			for(Attribute attributeToAdd : getDataSetDescription(setName)){
				
				attributeDataTypes.put(attributeToAdd.getName(), attributeToAdd.getDataType());
			}

			while(results.next()){
				
				Attribute[] attributes = new Attribute[attributeCount];

				for(int j = 0; j<attributeCount; ++j){

					String attributeName = meta.getColumnName(j+1);
					
					//Driver returns wrong data type - bug!
					// String dataType = meta.getColumnTypeName(j+1);
					
					// Get the data type another, not as good, way
					String dataType = attributeDataTypes.get(attributeName);

					attributes[j] = new Attribute(
													dataType,
													attributeName,
													setName,
													!(meta.isAutoIncrement(j+1) || meta.isNullable(j+1) == 2),
													stringToAttributeValueObject(results.getString(j+1), dataType)
												);
				}

				entities.add(attributes);
			}

			results.close(); // Free result set

			return entities.toArray(new Attribute[0][]);

		}
		catch(SQLException e){throw new IOException("Failed to execute the read operation.", e);}
		catch(DataTypeException e){throw new IOException("Failed to execute the read operation. Could not handle a data type in the data layer.", e);}
	}

	/**
	 * Updates an existing entity in the data layer or creates one if it does not already exist.
	 * Applies changes in attributes for the entity
	 * 
	 * @param setName		The name of the set to write to. Cannot be null or empty
	 * @param values		The attributes and their new values. Cannot be null
	 * @return				The id attribute of the updated entity.
	 * @throws IOException if the operation fails, for instance if mandatory attributes are missing or have no values
	 */
	public Attribute writeToDataLayer(String setName, Attribute[] values) throws IOException {

		if(setName == null) throw new NullPointerException("setName was null.");
		if(values == null) throw new NullPointerException("values was null.");

		if(setName.trim().length() == 0) throw new IllegalArgumentException("setName was empty.");

		// STEP 1: GENERATE SQL QUERY STRING

		String attributeNames = "";
		String attributeValues = "";

		if(values != null && values.length > 0){
			
			for(Attribute attribute : values){

				if(attribute == null) continue;

				attributeNames += "`"+attribute.getName()+"`, ";
				attributeValues += objectToSqlString(attribute.getValue())+", ";
			}

			attributeNames = attributeNames.substring(0, attributeNames.length()-2); // Remove the last ", "
			attributeValues = attributeValues.substring(0, attributeValues.length()-2); // Remove the last ", "
		}
		String sql = "INSERT OR REPLACE INTO `"+setName+"` ("+attributeNames+") VALUES ("+attributeValues+");";
		
		// STEP 2: QUERY DATABASE AND PROCESS RESULTS

		try{

			statement.executeUpdate(sql);

			// STEP 3: RETURN ID ATTRIBUTE

			ResultSet keys = statement.getGeneratedKeys();

			// Name of id attribute
			ResultSet primaryKeys = connection.getMetaData().getPrimaryKeys(null, null, setName);
			primaryKeys.next();
			String attributeName = primaryKeys.getString("COLUMN_NAME"); // There can only be 1 primary key per table. That primary key is the id
			primaryKeys.close();

			if(keys.next()){ // The id was created

				String dataType = "INTEGER"; // SQLite can only auto-increment (generate keys) for integer types.
				boolean isMandatory = false; // Auto-incremented columns allows null per nature
				Object value = stringToAttributeValueObject(keys.getString("last_insert_rowid()"), dataType);

				keys.close();

				return new Attribute(dataType, attributeName, setName, isMandatory, value);
			}
			else { // The id was passed

				for(Attribute attribute : values)	
					if(attribute.getName().equals(attributeName))	
						return attribute;

				throw new RuntimeException("Unexpected logic failure.");
			}
		}
		catch(SQLException e){throw new IOException("Failed to execute the write operation.", e);}
		catch(DataTypeException e){throw new IOException("Failed to execute the write operation - an unsupported data type was passed.", e);}
	}

	/**
	 * Deletes an existing entity in the data layer.
	 * 
	 * @param setName 		The name of the entity to delete. Cannot be null or empty
	 * @throws IOException	if the operation fails
	 */
	public void deleteFromDataLayer(String setName, DataCondition[] conditions) throws IOException {

		if(setName == null) throw new NullPointerException("setName was null!");
		if(setName.trim().length() == 0) throw new IllegalArgumentException("setName was empty!");
		
		// Step 1: Generate SQL query string

		String sql = "DELETE FROM `" + setName + "`" + sqlWhere(conditions, "OR");

		// Step 2: Query database and process results

		try{

			statement.executeUpdate(sql);
		} catch(SQLException e){
			throw new IOException("Failed to execute the delete operation.", e);
		}

	}

	/**
	 * Generates a 'WHERE' SQL string
	 * 
	 * @param conditions The conditions the 'WHERE' string should express
	 * @param statement The SQL statement (OR / AND)
	 * @return the SQL 'WHERE' string
	 */
	private String sqlWhere(DataCondition[] conditions, String statement){
		
		String sql = " WHERE";
			
		for(DataCondition condition : conditions){

			if(condition == null) continue;

			String operatorValue = condition.getReferenceValue() == null ? "" : objectToSqlString(condition.getReferenceValue());

			sql += " `"+condition.getAttributeName()+"` "+condition.getOperator().toSql()+" "+operatorValue+" "+statement;
		}
		return sql.substring(0, sql.length()-(statement.length()+1));// Remove the last statement
	}
}